PostgreSQL 中的时间
PG 提供了多个用于描述时间的数据类型:
- 时间戳
- timestamp
- timestamp with time zone
- 日期 (date)
- 时间 (time)
- 时间长度 (interval)
- 时间范围 (tsrange, tstzrange, daterange)
关于什么时候不应该使用什么数据类型，可以看这篇 PostgreSQL Don't Do This 中文译本 。
1. 时间戳
PG 按照 SQL 标准提供了两个时间戳类型：
- timestamp (timestamp without time zone) 不带时区
- timestamptz (timestamp with time zone) 带时区
多数情况下，应使用 timestamptz；不应将时间转换为 UTC 再存储到 timestamp 中。 PG 官方也 推荐这样做。
有一个误解: timestamptz 要存储时区，所以它比 timestamp 使用了更多存储空间。而实际上，timestamp 和 timestamptz 在 PG 都只占 8 字节，并且它们有相同的表示范围 (4713 BC - 294276 AD)、相同的分辨率(1 毫秒)。
PG 在存储 timestamptz 格式的数据时，会自动将时间转换为 UTC 时间再存储，也就是说，timestamptz 实际上存储的就是 UTC 时间。客户端查询时间时，数据库会自动把查到的时间转到客户端使用的时区再发给客户端。而 timestamp 完全无视时区，需要客户端自己来造轮子处理。
不让业务代码先把时间转 UTC 再存到 timestamp 的原因是:
- 处理时间相关的逻辑需要写更多的代码、有更多的负担，才能实现一个数据库已经提供的功能
- PG 把时区信息和夏令时信息处理得非常好 (
select * from pg_timezone_names;)，自己造的轮子不一定比 PG 自带的好用 - timestamptz 能够让
date_trunc()和generate_series()等函数在相应时区下正常工作
但是，有些情况下 timestamptz 也没那么好用:
- 现有业务严重依赖 timestamp，引入 timestamptz 会导致相当多额外的工作
- 非常关注未来的本地时间，而且用户所处时区有可能经常变化，这些情况下 PG 的轮子也帮不上忙
- 比如日历应用的提醒功能: 需要在本地时区的 7:00 am 推送消息。这时候不带时间戳的 timestamp 更好用
-- 设置当前会话的时区
-- 会话的时区也可以在数据库连接字符串 DSN 中指定，比如 timezone=Asia/Shanghai
set time zone "Asia/Shanghai";
-- 显示当前会话的时区
show timezone;
-- TimeZone
-- ---------------
-- Asia/Shanghai
-- (1 row)
-- 将字符串解析为带时区的时间，如果没有提供时区，以当前会话的时区为准
-- 也就是说
-- - 第一个时间唯一确定
-- - 第二个时间由于不带时区，其表示的时间根据会话的时区不同而不同
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
-- timestamptz | timestamp
-- ------------------------+---------------------
-- 2008-08-08 08:08:08+08 | 2008-08-08 08:08:08
-- (1 row)
-- 修改当前会话的时区到火奴鲁鲁的时区 (西十区)，写作时未在夏令时
set time zone 'Pacific/Honolulu';
select timestamptz '2008-08-08 08:08:08+08', timestamptz '2008-08-08 08:08:08';
-- timestamptz | timestamp
-- ------------------------+---------------------
-- 2008-08-07 14:08:08-10 | 2008-08-08 08:08:08
-- (1 row)
如果要将表中数据类型为 timestamp 的列转为 timestamptz 则可以:
-- 假设要被转换的 timestamp 列存了 UTC 时间
alter table <table-name>
alter <col-name> type timestamptz
using <col-name> at time zone 'UTC';
2. 日期
如果只关注日期而不关注时间，那么可以使用 date 这个数据类型，它占 4 字节，范围是 4713 BC - 587489 AD，分辨率是 1 天，并且没有时区概念。
date, timestamp, timestamptz 可以互相比较和转换:
set time zone 'Asia/Shanghai';
select timestamp '2008-08-08 00:00:00' = date '2008-08-08'; -- True
select timestamp '2008-08-08 00:00:00.000001' = date '2008-08-08'; -- False
select timestamptz '2008-08-08 00:00:00+08' = date '2008-08-08'; -- True
select timestamptz '2008-08-08 00:00:00+12' = date '2008-08-08'; -- False
set 命令设置了客户端的时区，那么 date 在和 timestamptz 比较时会自动转成 timestamptz 类型，即 2008-08-08 被转为 2008-08-08 00:00:00+08 。而 2008-08-08 00:00:00+12 会先被转成 2008-08-07 18:00:00+08 再和后面的值比较。
3. 时间
如果只关注一天中的某个点，而不关注到底是哪天，可以用 time 类型，它占 8 字节，存储范围是 00:00:00 - 24:00:00，分辨率 1 微秒。timetz 是为了完整实现 SQL 标准而做出来的数据类型，PG 不推荐使用它。
时间戳可以单向转换为时间:
set time zone 'Asia/Shanghai';
select timestamptz '2008-08-08 00:00:59+11'::time with time zone; -- 21:00:59+08
select timestamptz '2008-08-08 00:00:59+11'::time; -- 21:00:59+08
-- 无时区时间不能转带时区时间
select timestamp '2008-08-08 00:00:59'::time with time zone; -- ERROR
select timestamp '2008-08-08 00:00:59'::time; -- 00:00:59
4. 时间长度
interval 这个类型可以用来存储时间长度，它占 16 字节，存储范围是 +-178,000,000 年，分辨率是 1 微秒。
比起存储，它更多地被用于计算时间推移:
-- 北京奥运会开幕到2020元旦有多久了？
select timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08'; -- 4162 days 16:00:00
-- 多少天?
select extract(days from
timestamptz '2020-01-01 00:00:00+08' - timestamptz '2008-08-08 08:00:00+08'); -- 4162
-- 2020 年元旦的 12 年 12 天 12 小时之前是什么时候? (不关注时区)
select timestamp '2020-01-01 00:00:00' - interval '12 years 12 days 12 hours'; -- 2007-12-19 12:00:00
-- 按会话时区生成 2020 年元旦到 2021 年元旦，每 59 天的序列
select generate_series(timestamptz '2020-01-01 00:00:00+08',
timestamptz '2021-01-01 00:00:00+08', interval '59 days');
-- generate_series
-- ------------------------
-- 2020-01-01 00:00:00+08
-- 2020-02-29 00:00:00+08
-- 2020-04-28 00:00:00+08
-- 2020-06-26 00:00:00+08
-- 2020-08-24 00:00:00+08
-- 2020-10-22 00:00:00+08
-- 2020-12-20 00:00:00+08
-- (7 rows)
5. 时间范围
时间范围常用于预定类业务，可以大幅简化应用层代码。
tsrange, tstzrange 和 daterange 用于存储时间范围，它们采用的底层格式分别是 timestamp, timestamptz 和 date。
-- 可以使用开闭区间来表示是否包含边界
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08)'::tstzrange;
-- infinity 表示无限
-- infinity 一侧的开闭区间可以随意选择，但在数学上通常写为开区间
select '[2020-01-01 00:00:00+08, infinity)'::tstzrange;
-- 两个区间是否相交
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]'::tstzrange
&& '[2020-01-01 00:00:00+08, 2020-01-01 17:15:00+08)'::tstzrange; -- True
-- 求交集
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]'::tstzrange
* '[2020-01-01 08:00:00+08, 2020-01-01 17:15:00+08)'::tstzrange;
-- ?column?
-- ---------------------------------------------------
-- ["2020-01-01 08:00:00+08","2020-01-01 12:25:00+08"]
-- 求差集
select '[2020-01-01 00:00:00+08, 2020-01-01 12:25:00+08]'::tstzrange
- '[2020-01-01 08:00:00+08, 2020-01-01 17:15:00+08)'::tstzrange;
-- ?column?
-- -----------------------------------------------------
-- ["2020-01-01 00:00:00+08","2020-01-01 08:00:00+08")
比如，对于一个会议室预定系统来说，每个会议室在同一时间段只能预约一次，而且时间段不能重叠:
-- PG 自带扩展，为普通类型添加 GIST 索引运算符支持
CREATE EXTENSION btree_gist;
-- 会议室预定表
CREATE TABLE meeting_room
(
id SERIAL PRIMARY KEY,
user_id INTEGER,
room_id INTEGER,
range tsrange,
EXCLUDE USING GIST(room_id WITH = , range WITH &&)
);
-- 用户 1 预定 101 号会议室，从早上 10 点到下午 6 点
INSERT INTO meeting_room(user_id, room_id, range)
VALUES (1,101, tsrange('2019-01-01 10:00', '2019-01-01 18:00'));
-- 用户 2 预定 101 号会议室，下午 4 点到下午 6 点
INSERT INTO meeting_room(user_id, room_id, range)
VALUES (2,101, tsrange('2019-01-01 16:00', '2019-01-01 18:00'));
-- 用户2的预定报错，违背了排它约束
ERROR: conflicting key value violates exclusion constraint "meeting_room_room_id_range_excl"
DETAIL: Key (room_id, range)=(101, ["2019-01-01 16:00:00","2019-01-01 18:00:00")) conflicts with existing key (room_id, range)=(101, ["2019-01-01 10:00:00","2019-01-01 18:00:00")).